跳到主要内容

SQL Server 运维常用sql语句（一）

一、基础命令​

查看当前数据库的版本​

SELECT @@VERSION;

查看服务器部分特殊信息​

select SERVERPROPERTY(N'edition') as Edition     --数据版本，如企业版、开发版等

    ,SERVERPROPERTY(N'collation') as Collation   --数据库字符集

    ,SERVERPROPERTY(N'servername') as ServerName --服务名

    ,@@VERSION as Version   --数据库版本号

    ,@@LANGUAGE AS Language  --数据库使用的语言，如us_english等

获取数据库当前时间​

SELECT GETDATE() AS CurrentDateTime;

查看数据库启动的参数​

sp_configure

查看所有数据库用户登录信息​

sp_helplogins

查看数据库启动时间（最近一次）​

select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1

查看有多少个端口​

SELECT * FROM sys.dm_tcp_listener_states;

查看当前的连接数​

SELECT COUNT(*) AS [Connection Count] FROM sys.dm_exec_connections;

查看各个磁盘分区的剩余空间​

Exec master.dbo.xp_fixeddrives

查看数据库的磁盘使用情况​

Exec sp_spaceused

查看数据库服务器各数据库日志文件的大小及利用率​

DBCC SQLPERF(LOGSPACE)

查看当前占用 cpu 资源最高的会话和其中执行的语句​

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from master..sysprocesses order by cpu desc,physical_io desc

查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）​

SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] 

FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql 

ORDER BY usecounts,p.size_in_bytes  desc

看BUFFER POOL中，都缓存了哪些表(当前数据库)的数据​

select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb                              

from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              

where  a.allocation_unit_id=b.allocation_unit_id   

       and b.container_id=c.hobt_id             

       and database_id=DB_ID()                              

group by OBJECT_NAME(object_id)                           

order by 2 desc

查看用户的权限​

EXEC sp_helprotect;

查看当前数据库内存使用情况​

select * from sys.dm_os_process_memory

查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量​

-- 查询当前数据库缓存的所有数据页面，哪些数据表，缓存的数据页面数量

-- 从这些信息可以看出，系统经常要访问的都是哪些表，有多大？

select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) 

from sys.allocation_units a, 

    sys.dm_os_buffer_descriptors b, 

    sys.partitions p 

where a.allocation_unit_id=b.allocation_unit_id 

    and a.container_id=p.hobt_id 

    and b.database_id=db_id()

group by p.object_id,p.index_id 

order by buffer_pages desc

查询缓存中具体的执行计划，及对应的SQL​

-- 查询缓存中具体的执行计划，及对应的SQL

-- 将此结果按照数据表或SQL进行统计，可以作为基线，调整索引时考虑

-- 查询结果会很大，注意将结果集输出到表或文件中

SELECT  usecounts ,

        refcounts ,

        size_in_bytes ,

        cacheobjtype ,

        objtype ,

        TEXT

FROM    sys.dm_exec_cached_plans cp

        CROSS APPLY sys.dm_exec_sql_text(plan_handle)

ORDER BY objtype DESC ;

GO

查看具体某个用户的权限​

SELECT p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc, u.name AS user_name
FROM sys.database_permissions p
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE u.name = 'test'

查看注册时的实例名​

SELECT * FROM sys.servers;

查询用户角色​

select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid  

from sys.server_principals u, sys.server_principals g, sys.server_role_members m  

where g.principal_id = m.role_principal_id  

and u.principal_id = m.member_principal_id  

order by 1, 2  

go

看服务器角色​

select 用户名 = u.name,管理员权限 = g.name,是否在用 = u.is_disabled,MemberSID = u.sid  

from sys.server_principals u, sys.server_principals g, sys.server_role_members m  

where g.principal_id = m.role_principal_id  

and u.principal_id = m.member_principal_id  

and g.name = 'sysadmin'

order by 1, 2

go

查询当前用户所有用户表​

select name from sysobjects where xtype='u' order by name

查看所有的数据库​

Select Name FROM Master..SysDatabases orDER BY Name

查看服务器角色相关信息​

SP_HELPSRVROLE 

SP_HELPSRVROLEMEMBER 服务器角色 

SP_HELPSRVROLE 服务器角色

查看数据库角色相关信息​

SP_HELPROLE 

SP_HELPROLEMEMBER 数据库角色 

SP_HELPROLE 数据库角色

查看用户相关信息​

SP_HELPUSER 

SP_HELPUSER 数据库用户名

查看上次启动以来尝试的连接数​

select @@connections //返回 SQL Server 自上次启动以来尝试的连接数，无论连接是成功还是失败

当前实例允许同时进行的最大用户连接数​

select @@max_connections 

//返回 SQL Server 实例允许同时进行的最大用户连接数。返回的数值不一定是当前配置的数值

查询当前最大的连接数​

SELECT value_in_use

FROM sys.configurations c

WHERE c.name = 'user connections'; #0表示无限制

设置修改连接数​

exec sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

exec sp_configure 'user connections', 300

RECONFIGURE WITH OVERRIDE

查询当前会话超时时间​

select @@lock_timeout //返回当前会话的当前锁定超时设置（毫秒）。

查询每个用户的连接数​

select loginame,count(1) as Nums

from sys.sysprocesses

group by loginame

order by 2 desc

select spid,ecid,status,loginame,hostname,cmd,request_id 

from sys.sysprocesses where loginame='' and hostname=''

查看当前活动的实例​

SELECT CURRENT_USER AS [Current User], SESSION_USER AS [Session User];

查看当前活动进程​

SELECT * FROM sys.dm_exec_requests;

查看所有数据库的大小​

SELECT 

    DB_NAME(database_id) AS DatabaseName,

    SUM(size/128.0) AS SizeInMB,

    SUM(size/128.0)/1024 AS SizeInGB

FROM 

    sys.master_files

GROUP BY 

    database_id

ORDER BY 

    SizeInMB DESC;

查看某个数据库的大小​

SELECT sys.databases.name AS [Database Name], 

    CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10,2)) AS [Size (MB)]

FROM sys.master_files

INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id

WHERE sys.databases.name = 'master'

GROUP BY sys.databases.name;

#也可以用EXEC sp_spaceused @updateusage = N'TRUE';

查看当前数据库的日志大小​

SELECT sys.databases.name AS [Database Name], 

    CAST(size * 8 / 1024.0 AS DECIMAL(10,2)) AS [Log File Size (MB)]

FROM sys.master_files

INNER JOIN sys.databases ON sys.master_files.database_id = sys.databases.database_id

WHERE sys.databases.name = 'master'

    AND sys.master_files.type = 1;

查询当前数据库的表和视图​

SELECT TABLE_NAME AS [Table/View Name], TABLE_TYPE AS [Type]

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW');

查询表结构信息​

sp_help 'test';

二、运维小技巧​

一次性清除数据库所有表的数据（高危操作，谨慎）​

CREATE PROCEDURE sp_DeleteAllData  

AS  

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  

EXEC sp_MSForEachTable 'DELETE FROM ?'  

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'  

EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'  

GO

数据备份与恢复​

备份​

BACKUP DATABASE test TO DISK = 'C:\backup\MyDatabase.bak';

恢复​

RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';

完整数据库备份​

BACKUP DATABASE test

TO DISK = 'C:\Backup\MyDatabase.bak'

WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup';

差异备份​

BACKUP DATABASE test

TO DISK = 'C:\Backup\MyDatabase_diff.bak'

WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup';

事务日志备份​

BACKUP LOG test

TO DISK = 'C:\Backup\MyDatabase_log.trn'

WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

还原数据库​

RESTORE DATABASE test

FROM DISK = 'C:\Backup\MyDatabase.bak'

WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;

创建账户及数据库用户​

#创建账户

CREATE LOGIN test WITH PASSWORD = '123123';

#创建数据库用户并映射到登录名

CREATE USER test FOR LOGIN test;

ALTER ROLE db_datareader ADD MEMBER test; -- 给予读权限

ALTER ROLE db_datawriter ADD MEMBER test; -- 给予写权限

SQL优化相关、执行时间​

SELECT creation_time  N'语句编译时间'  

        ,last_execution_time  N'上次执行时间'  

        ,total_physical_reads N'物理读取总次数'  

        ,total_logical_reads/execution_count N'每次逻辑读次数'  

        ,total_logical_reads  N'逻辑读取总次数'  

        ,total_logical_writes N'逻辑写入总次数'  

        ,execution_count  N'执行次数'  

        ,total_worker_time/1000 N'所用的CPU总时间ms'  

        ,total_elapsed_time/1000  N'总花费时间ms'  

        ,(total_elapsed_time / execution_count)/1000  N'平均时间ms'  

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  

         ((CASE statement_end_offset   

          WHEN -1 THEN DATALENGTH(st.text)  

          ELSE qs.statement_end_offset END   

            - qs.statement_start_offset)/2) + 1) N'执行语句'  

FROM sys.dm_exec_query_stats AS qs  

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  

WHERE SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,  

         ((CASE statement_end_offset   

          WHEN -1 THEN DATALENGTH(st.text)  

          ELSE qs.statement_end_offset END   

            - qs.statement_start_offset)/2) + 1) NOT LIKE '%fetch%'  

ORDER BY  total_elapsed_time / execution_count DESC;

查看job运行持续时间​

SELECT 

     [T1].[job_id]

    ,[T1].[name] AS [job_name] 

    ,[T2].[run_status]

    ,[T2].[run_date]

    ,[T2].[run_time]

    ,[dbo].[agent_datetime]([T2].[run_date], [T2].[run_time]) AS [run_datetime]

    ,[T2].[run_duration]

    ,DATEDIFF(SECOND, '1900-01-01', DATEADD(SECOND, 31, [dbo].[agent_datetime](19000101, [run_duration]))) AS [run_duration_s]

FROM 

    [dbo].[sysjobs] AS T1

    INNER JOIN [dbo].[sysjobhistory] AS T2

        ON [T2].[job_id] = [T1].[job_id]

WHERE 

    [T1].[enabled] = 1

    AND [T2].[step_id] = 0

    AND [T2].[run_duration] >= 1

    and [T1].[name]='PIMS_CreatePaperCraftParameterAnalysisData'

ORDER BY

     [T2].[job_id] ASC

    ,[T2].[run_date] ASC

GO

从所有缓存中释放所有未使用的缓存条目​

DBCC FREESYSTEMCACHE('ALL');

查询、解除死锁​

--查询表死锁信息

select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks

where resource_type = 'OBJECT'



dbcc opentran



--查看死锁的详细信息、执行的sql语句

exec sp_who2 53

--exec sp_who 53 

DBCC inputbuffer (53)



--解除死锁

kill 53

查询SQL Server根据CPU消耗列出前5个最差性能的查询​

-- Worst performing CPU bound queries

SELECT TOP 5

    st.text,

    qp.query_plan,

    qs.*

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_worker_time DESC

GO

查询数据库中各数据表大小​

-- =============================================

-- 描  述：更新查询数据库中各表的大小，结果存储到数据表中

-- =============================================



    --查询是否存在结果存储表

    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

    BEGIN

        --不存在则创建

        CREATE TABLE temp_tableSpaceInfo

        (name NVARCHAR(128), 

        rows char(11), 

        reserved VARCHAR(18),

        data VARCHAR(18),

        index_size VARCHAR(18),

        unused VARCHAR(18))

    END

    --清空数据表

    DELETE FROM temp_tableSpaceInfo



    --定义临时变量在遍历时存储表名称

    DECLARE @tablename VARCHAR(255)



    --使用游标读取数据库内所有表表名

    DECLARE table_list_cursor CURSOR FOR 

    SELECT name FROM sysobjects 

    WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name



    --打开游标

    OPEN table_list_cursor

    --读取第一条数据

    FETCH NEXT FROM table_list_cursor INTO @tablename 



    --遍历查询到的表名

    WHILE @@FETCH_STATUS = 0

    BEGIN

        --检查当前表是否为用户表

        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

        BEGIN

            --当前表则读取其信息插入到表格中

            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename

        END

        --读取下一条数据

        FETCH NEXT FROM table_list_cursor INTO @tablename 

    END



    --释放游标

    CLOSE table_list_cursor

    DEALLOCATE table_list_cursor



    SELECT *,replace(reserved,'KB','')/1024 数据表大小M FROM temp_tableSpaceInfo order by replace(reserved,'KB','')/1024 desc

    drop table temp_tableSpaceInfo

显示如何依据I/O消耗来找出你性能最差的查询​

-- Worst performing I/O bound queries

SELECT TOP 5

    st.text,

    qp.query_plan,

    qs.*

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY total_logical_reads DESC

GO

压缩数据库、文件、日志​

DBCC ShrinkFile(‘数据库名’,  targetsize);            /* 收缩数据库文件 */

DBCC ShrinkFile(‘数据库名_log’,  targetsize);        /* 收缩日志文件 */

Targetsize：单位为兆，必须为整数，DBCC SHRINKFILE 尝试将文件收缩到指定大小。



DBCC SHRINKFILE 不会将文件收缩到小于“实际使用的空间”大小，例如“分配空间”为10M，“实际使用空间”为6M，当制定targetsize为1时，则将该文件收缩到6M，不会将文件收缩到1M。



--收缩数据库

DBCC SHRINKDATABASE(数据库名，百分比)

百分比：即“收缩后文件中的最大可用空间”，取值范围“大于等于0， 小于100%”，实际使用中设为0即可。

查询数据库表字段各项属性信息，便于直接复制导出excel表​

SELECT  

     表名       = Case When A.colorder=1 Then D.name Else '' End,  

     表说明     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,  

     字段序号   = A.colorder,  

     字段名     = A.name,  

     字段说明   = isnull(G.[value],''),  

     标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,  

     主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (  

                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,  

     类型       = B.name,  

     占用字节数 = A.Length,  

     长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),  

     小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),  

     允许空     = Case When A.isnullable=1 Then '√'Else '' End,  

     默认值     = isnull(E.Text,'')  

 FROM  

     syscolumns A  

 Left Join  

     systypes B  

 On  

     A.xusertype=B.xusertype  

 Inner Join  

     sysobjects D  

 On  

     A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'  

 Left Join  

     syscomments E  

 on  

     A.cdefault=E.id  

 Left Join  

 sys.extended_properties  G  

 on  

     A.id=G.major_id and A.colid=G.minor_id  

 Left Join  



 sys.extended_properties F  

 On  

     D.id=F.major_id and F.minor_id=0  

     --where d.name='OrderInfo'    --如果只查询指定表,加上此条件  

 Order By  

     A.id,A.colorder

数据库缓存清理​

CREATE PROCEDURE [dbo].ClearMemory   

AS   

BEGIN 

    --清除所有缓存 

    DBCC DROPCLEANBUFFERS 

    --打开高级配置 

    exec sp_configure 'show advanced options', 1 

    --设置最大内存值，清除现有缓存空间 

    exec sp_configure 'max server memory', 25600 

    EXEC ('RECONFIGURE') 

    --设置等待时间 

    WAITFOR DELAY '00:00:01' 

    --重新设置最大内存值 

    EXEC  sp_configure 'max server memory',40960 

    EXEC ('RECONFIGURE') 

    --关闭高级配置 

    exec sp_configure 'show advanced options',0 

END

GO

三、日常运维操作​

数据库用户、权限操作​

USE [master]

GO

--待确认账号密码

CREATE LOGIN [NDIT] WITH PASSWORD=N'1', DEFAULT_DATABASE=[PIMS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE PIMS

go

CREATE USER [NDIT] FOR LOGIN [NDIT]

GO

--大权限， 如果是指定的部分表，不执行这个，如果是所有内容都可以读，用此脚本

--EXEC sp_addrolemember N'db_datareader', N'NDIT'

--GO

--指定特定表名赋予新增/更新/查询

DECLARE @Sql NVARCHAR(max)

SET @Sql=''

--table

--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.tables AS a WHERE name IN ('Tab1','Tab2');

--view

--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.views AS a WHERE name IN ('view1','view2');

--procedure

--SELECT @Sql=@Sql+'GRANT INSERT,UPDATE,SELECT ON ['+a.name+'] TO [NDIT];' FROM sys.procedures AS a WHERE name IN ('proc1','proc2');



PRINT @Sql



EXEC(@Sql)

go



--禁用登陆帐户

alter login NDIT disable

--启用登陆帐户

alter login NDIT enable



--登陆帐户改名

alter login NDIT with name=dba_tom



--登陆帐户改密码： 

alter login NDIT with password='aabb@ccdd'



--数据库用户改名： 

alter user NDIT with name=dba_tom



--更改数据库用户 defult_schema： 

alter user NDIT with default_schema=sales



--删除数据库用户： 

drop user NDIT



--删除 SQL Server登陆帐户： 

drop login NDIT